﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ORM.Business.Feedbacks;
using ORM.Business.Interfaces;
using ORM.Business.Models.Entities;
using ORM.Resources;
using ORM.Common.Logging;
using ORM.Business.Models;
namespace ORM.Business.Repositories
{
    public class GuideRepository : IGuideRepository
    {
        OrmEntities ORMDbContext = new OrmEntities();

        public GuideListFeedback GetAllGuides()
        {

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in ORMDbContext.Guides
                                join u in ORMDbContext.Users on q.PostedBy equals u.Id
                                join m in ORMDbContext.Models on q.ModelID equals m.Id
                                join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                join gt in ORMDbContext.GuideTypes on q.GuideTypeId equals gt.Id
                                where !q.IsDeleted && q.isApproved
                                orderby q.PostedDate descending
                                select new
                                {
                                    q.Id,
                                    q.GuideTypeId,
                                    q.Title,
                                    q.TitleUrl,
                                    q.PostedDate,
                                    q.Views,
                                    q.Point,
                                    q.UpVotes,
                                    q.DownVotes,
                                    q.ContentHtml,
                                    q.Comments,
                                    q.PostedBy,
                                    u.Username,
                                    q.ModelID,
                                    DeviceId = d.Id,
                                    ManufacturerId = mf.Id,
                                    ModelName = m.Name,
                                    DeviceName = d.Name,
                                    ManufacturerName = mf.Name,
                                    GuideTypeName = gt.Name,
                                };

                    var total = query.Count();
                    var guideList = new List<Guide>();
                    foreach (var guide in query)
                    {
                        guideList.Add(new Guide
                        {
                            Id = guide.Id,
                            GuideTypeId = guide.GuideTypeId,
                            Title = guide.Title,
                            TitleUrl = guide.TitleUrl,
                            PostedDate = guide.PostedDate,
                            Views = guide.Views,
                            Point = guide.Point,
                            UpVotes = guide.UpVotes,
                            DownVotes = guide.DownVotes,
                            ContentHtml = guide.ContentHtml,
                            Comments = guide.Comments,
                            PostedByUser = new user { Id = guide.PostedBy, Username = guide.Username },
                            //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                            Model = new Model { Id = guide.ModelID, Name = guide.ModelName, Manufacturer = new Manufacturer { Name = guide.ManufacturerName }, Device = new Device { Name = guide.DeviceName } },
                            GuideType = new GuideType {Id = guide.GuideTypeId, Name = guide.GuideTypeName}
                        });
                    }
                    return new GuideListFeedback(true, null, guideList, total);
                }

            }
            catch (Exception e)
            {

                return new GuideListFeedback(false, "Cannot get list guide");
            }
        }
        public Feedback<IList<Guide>> GetTopNewGuides()
        {

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in ORMDbContext.Guides
                                //join u in ORMDbContext.Users on q.PostedBy equals u.Id
                                //join m in ORMDbContext.Models on q.ModelID equals m.Id
                                //join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                //join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                //join gt in ORMDbContext.GuideTypes on q.GuideTypeId equals gt.Id
                                where !q.IsDeleted && q.isApproved
                                orderby q.PostedDate descending
                                select new
                                {
                                    q.Id,
                                    //q.GuideTypeId,
                                    q.Title,
                                    //q.TitleUrl,
                                    //q.PostedDate,
                                    //q.Views,
                                    //q.Point,
                                    //q.UpVotes,
                                    //q.DownVotes,
                                    //q.ContentHtml,
                                    //q.Comments,
                                    //q.PostedBy,
                                    //u.Username,
                                    //q.ModelID,
                                    //DeviceId = d.Id,
                                    //ManufacturerId = mf.Id,
                                    //ModelName = m.Name,
                                    //DeviceName = d.Name,
                                    //ManufacturerName = mf.Name,
                                    //GuideTypeName = gt.Name,
                                };
                   
                    var guideList = new List<Guide>();
                    query = query.Take(KeyObject.TopItemNumber.GuideNumber);
                    foreach (var guide in query)
                    {
                        guideList.Add(new Guide
                        {
                            Id = guide.Id,
                            //GuideTypeId = guide.GuideTypeId,
                            Title = guide.Title,
                            //TitleUrl = guide.TitleUrl,
                            //PostedDate = guide.PostedDate,
                            //Views = guide.Views,
                            //Point = guide.Point,
                            //UpVotes = guide.UpVotes,
                            //DownVotes = guide.DownVotes,
                            //ContentHtml = guide.ContentHtml,
                            //Comments = guide.Comments,
                            //PostedByUser = new user { Id = guide.PostedBy, Username = guide.Username },
                            ////ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                            //Model = new Model { Id = guide.ModelID, Name = guide.ModelName, Manufacturer = new Manufacturer { Name = guide.ManufacturerName }, Device = new Device { Name = guide.DeviceName } },
                            //GuideType = new GuideType { Id = guide.GuideTypeId, Name = guide.GuideTypeName }
                        });
                    }
                    return new Feedback<IList<Guide>>(true, null, guideList);
                }

            }
            catch (Exception e)
            {

                return new Feedback<IList<Guide>>(false, Messages.GeneralError);
            }
        }
        public GuideListFeedback GetSimilarGuides(int modelId, string stringToSearch)
        {

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    //var queryString = "SELECT TOP 5 * FROM Guides WHERE isDeleted='false' and modelId={0} and dbo.Levenstein({1}, Title) > 0 order by dbo.Levenstein({1}, Title) desc";
                    //var queryString =
                    //       "SELECT TOP 5 p.* FROM dbo.Guides AS p INNER JOIN FREETEXTTABLE(Guides, Title, {0}, LANGUAGE 'Vietnamese') AS ft ON ft.[KEY] = p.Id and isDeleted = 'false' and isApproved='true' and modelId={1} ORDER BY ft.[Rank] DESC";
                       
                    //var query = (ORMDbContext as System.Data.Entity.Infrastructure.IObjectContextAdapter)
                    //      .ObjectContext.ExecuteStoreQuery<Guide>(queryString, stringToSearch, modelId).ToList();
                    var queryString =
                          "SELECT TOP 5 p.* FROM dbo.Guides AS p INNER JOIN FREETEXTTABLE(Guides, Title, {0}, LANGUAGE 'Vietnamese') AS ft ON ft.[KEY] = p.Id and isDeleted = 'false' and isApproved='true' ORDER BY ft.[Rank] DESC";

                    var query = (ORMDbContext as System.Data.Entity.Infrastructure.IObjectContextAdapter)
                          .ObjectContext.ExecuteStoreQuery<Guide>(queryString, stringToSearch).ToList();
                    var total = query.Count();
                    var guideList = new List<Guide>();
                    foreach (var guide in query)
                    {
                        guideList.Add(new Guide
                        {
                            Id = guide.Id,
                            Title = guide.Title,
                        });
                    }
                    return new GuideListFeedback(true, null, guideList, total);

                }
            }
            catch (Exception e)
            {

                return new GuideListFeedback(false, "Cannot get list guide");
            }
        }
        public Feedback<IList<Guide>> GetAllUnapprovedGuides()
        {

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in ORMDbContext.Guides
                                join u in ORMDbContext.Users on q.PostedBy equals u.Id
                                join m in ORMDbContext.Models on q.ModelID equals m.Id
                                join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                join gt in ORMDbContext.GuideTypes on q.GuideTypeId equals gt.Id
                                where !q.IsDeleted && !q.isApproved
                                orderby q.PostedDate descending
                                select new
                                {
                                    q.Id,
                                    q.GuideTypeId,
                                    q.Title,
                                    q.TitleUrl,
                                    q.PostedDate,
                                    q.Views,
                                    q.Point,
                                    q.UpVotes,
                                    q.DownVotes,
                                    q.ContentHtml,
                                    q.Comments,
                                    q.PostedBy,
                                    u.Username,
                                    q.ModelID,
                                    DeviceId = d.Id,
                                    ManufacturerId = mf.Id,
                                    ModelName = m.Name,
                                    DeviceName = d.Name,
                                    ManufacturerName = mf.Name,
                                    GuideTypeName = gt.Name,
                                };

                    var total = query.Count();
                    var guideList = new List<Guide>();
                    foreach (var guide in query)
                    {
                        guideList.Add(new Guide
                        {
                            Id = guide.Id,
                            GuideTypeId = guide.GuideTypeId,
                            Title = guide.Title,
                            TitleUrl = guide.TitleUrl,
                            PostedDate = guide.PostedDate,
                            Views = guide.Views,
                            Point = guide.Point,
                            UpVotes = guide.UpVotes,
                            DownVotes = guide.DownVotes,
                            ContentHtml = guide.ContentHtml,
                            Comments = guide.Comments,
                            PostedByUser = new user { Id = guide.PostedBy, Username = guide.Username },
                            //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                            Model = new Model { Id = guide.ModelID, Name = guide.ModelName, Manufacturer = new Manufacturer { Name = guide.ManufacturerName }, Device = new Device { Name = guide.DeviceName } },
                            GuideType = new GuideType { Id = guide.GuideTypeId, Name = guide.GuideTypeName }
                        });
                    }
                    
                    return new Feedback<IList<Guide>>(true, null, guideList);
                }

            }
            catch (Exception e)
            {

                return new Feedback<IList<Guide>>(false, Messages.GeneralError);
                
            }
        }
        public Feedback<IList<Guide>> GetAllApprovedGuides()
        {

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in ORMDbContext.Guides
                                join u in ORMDbContext.Users on q.PostedBy equals u.Id
                                join m in ORMDbContext.Models on q.ModelID equals m.Id
                                join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                join gt in ORMDbContext.GuideTypes on q.GuideTypeId equals gt.Id
                                where !q.IsDeleted && q.isApproved
                                orderby q.PostedDate descending
                                select new
                                {
                                    q.Id,
                                    q.GuideTypeId,
                                    q.Title,
                                    q.TitleUrl,
                                    q.PostedDate,
                                    q.Views,
                                    q.Point,
                                    q.UpVotes,
                                    q.DownVotes,
                                    q.ContentHtml,
                                    q.Comments,
                                    q.PostedBy,
                                    u.Username,
                                    q.ModelID,
                                    DeviceId = d.Id,
                                    ManufacturerId = mf.Id,
                                    ModelName = m.Name,
                                    DeviceName = d.Name,
                                    ManufacturerName = mf.Name,
                                    GuideTypeName = gt.Name,
                                };

                    var total = query.Count();
                    var guideList = new List<Guide>();
                    foreach (var guide in query)
                    {
                        guideList.Add(new Guide
                        {
                            Id = guide.Id,
                            GuideTypeId = guide.GuideTypeId,
                            Title = guide.Title,
                            TitleUrl = guide.TitleUrl,
                            PostedDate = guide.PostedDate,
                            Views = guide.Views,
                            Point = guide.Point,
                            UpVotes = guide.UpVotes,
                            DownVotes = guide.DownVotes,
                            ContentHtml = guide.ContentHtml,
                            Comments = guide.Comments,
                            PostedByUser = new user { Id = guide.PostedBy, Username = guide.Username },
                            //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                            Model = new Model { Id = guide.ModelID, Name = guide.ModelName, Manufacturer = new Manufacturer { Name = guide.ManufacturerName }, Device = new Device { Name = guide.DeviceName } },
                            GuideType = new GuideType { Id = guide.GuideTypeId, Name = guide.GuideTypeName }
                        });
                    }

                    return new Feedback<IList<Guide>>(true, null, guideList);
                }

            }
            catch (Exception e)
            {

                return new Feedback<IList<Guide>>(false, Messages.GeneralError);

            }
        }
        public Feedback ApproveGuide(int id)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    //Approve Guide
                    Guide guide = ORMDbContext.Guides.Single(i => i.Id == id);
                    guide.isApproved = true;
                    ORMDbContext.SaveChanges();
                    //Plus point for user
                    user user = ORMDbContext.Users.Find(guide.PostedBy);
                    user.Ponit += KeyObject.PointConfig.CreateGuide;
                    ORMDbContext.SaveChanges();
                    return new Feedback(true);
                }
            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback(false, Messages.GeneralError);
            }
        }
        public Feedback DisApproveGuide(int id)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    Guide guide = ORMDbContext.Guides.Single(i => i.Id == id);
                    guide.IsDeleted = true;
                    ORMDbContext.SaveChanges();

                    return new Feedback(true);
                }
            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback(false, Messages.GeneralError);
            }
        }

        public Feedback UnapproveGuide(int id)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    Guide a = ORMDbContext.Guides.Single(i => i.Id == id);
                    a.isApproved = false;
                    ORMDbContext.SaveChanges();

                    return new Feedback(true);
                }
            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback(false, Messages.GeneralError);
            }
        }

        public GuideListFeedback GetAllGuide(PageInfo pageInfo, int deviceId, int manufacturerId, int modelId, string stringToSearch, int guideTypeId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    if (stringToSearch != null && stringToSearch != "")
                    {
                        //var queryString = "SELECT * FROM Guides WHERE isDeleted='false' and isApproved='true' and dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) > 0 order by dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) desc, PostedDate";
                        var queryString =
                            "SELECT p.* FROM dbo.Guides AS p INNER JOIN FREETEXTTABLE(Guides, Title, {0}, LANGUAGE 'Vietnamese') AS ft ON ft.[KEY] = p.Id and isDeleted = 'false' and isApproved='true' ORDER BY ft.[Rank] DESC, PostedDate";
                        
                        var query = (ORMDbContext as System.Data.Entity.Infrastructure.IObjectContextAdapter)
                              .ObjectContext.ExecuteStoreQuery<Guide>(queryString, stringToSearch).ToList();
                        var total = query.Count();
                        var result = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var GuideList = new List<Guide>();
                        foreach (var Guide in result)
                        {
                            Guide.Model = ORMDbContext.Models.SingleOrDefault(m => m.Id == Guide.ModelID);
                            var device = ORMDbContext.Devices.SingleOrDefault(m => m.Id == Guide.Model.DeviceId);
                            var manufacturer = ORMDbContext.Manufacturers.SingleOrDefault(m => m.Id == Guide.Model.ManufacturerId);
                            GuideList.Add(new Guide
                            {
                                Id = Guide.Id,
                                Title = Guide.Title,
                                TitleSearch = Guide.TitleSearch,
                                TitleUrl = Guide.TitleUrl,
                                PostedDate = Guide.PostedDate,
                                Views = Guide.Views,
                                Point = Guide.Point,
                                UpVotes = Guide.UpVotes,
                                DownVotes = Guide.DownVotes,
                                ContentHtml = Guide.ContentHtml,
                                Comments = Guide.Comments,
                                PostedByUser = ORMDbContext.Users.SingleOrDefault(u => u.Id == Guide.PostedBy),
                                //ModelWithAllInfo = new ModelWithAllInformation { Id = Guide.ModelId, ModelName = Guide.ModelName, DeviceName = Guide.DeviceName, ManufactureName = Guide.ManufacturerName }   
                                Model = new Model { Id = Guide.ModelID, Name = Guide.Model.Name, Manufacturer = new Manufacturer { Id = manufacturer.Id, Name = manufacturer.Name }, Device = new Device { Id = device.Id, Name = device.Name } },
                                GuideType = ORMDbContext.GuideTypes.SingleOrDefault(gt => gt.Id == Guide.GuideTypeId)
                            });
                        }
                        return new GuideListFeedback(true, null, GuideList, total);
                    }
                    else
                    {
                        var query = from q in ORMDbContext.Guides
                                    join u in ORMDbContext.Users on q.PostedBy equals u.Id
                                    join m in ORMDbContext.Models on q.ModelID equals m.Id
                                    join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                    join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                    join gt in ORMDbContext.GuideTypes on q.GuideTypeId equals gt.Id
                                    where !q.IsDeleted && q.isApproved
                                    orderby q.PostedDate descending
                                    select new
                                    {
                                        q.Id,
                                        q.GuideTypeId,
                                        q.Title,
                                        q.TitleSearch,
                                        q.TitleUrl,
                                        q.PostedDate,
                                        q.Views,
                                        q.Point,
                                        q.UpVotes,
                                        q.DownVotes,
                                        q.ContentHtml,
                                        q.ContentText,
                                        q.Comments,
                                        q.PostedBy,
                                        u.Username,
                                        u.AvatarUrl,
                                        q.ModelID,
                                        DeviceId = d.Id,
                                        ManufacturerId = mf.Id,
                                        ModelName = m.Name,
                                        DeviceName = d.Name,
                                        ManufacturerName = mf.Name,
                                        GuideTypeName = gt.Name,
                                    };
                        if (modelId != 0)
                        {
                            query = query.Where(q => q.ModelID == modelId);
                        }
                        else
                        {
                            if (deviceId != 0)
                            {
                                query = query.Where(q => q.DeviceId == deviceId);
                            }
                            if (manufacturerId != 0)
                            {
                                query = query.Where(q => q.ManufacturerId == manufacturerId);
                            }
                        }
                        if (guideTypeId != 0)
                        {
                            query = query.Where(q => q.GuideTypeId == guideTypeId);
                        }
                        var total = query.Count();
                        query = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var guideList = new List<Guide>();
                        foreach (var guide in query)
                        {
                            guideList.Add(new Guide
                                                 {
                                                     Id = guide.Id,
                                                     GuideTypeId = guide.GuideTypeId,
                                                     Title = guide.Title,
                                                     TitleSearch = guide.TitleSearch,
                                                     TitleUrl = guide.TitleUrl,
                                                     PostedDate = guide.PostedDate,
                                                     Views = guide.Views,
                                                     Point = guide.Point,
                                                     UpVotes = guide.UpVotes,
                                                     DownVotes = guide.DownVotes,
                                                     ContentHtml = guide.ContentHtml,
                                                     Comments = guide.Comments,
                                                     PostedByUser = ORMDbContext.Users.SingleOrDefault(u => u.Id == guide.PostedBy),
                                                     //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                                                     Model = new Model { Id = guide.ModelID, Name = guide.ModelName, Manufacturer = new Manufacturer { Id = guide.ManufacturerId, Name = guide.ManufacturerName }, Device = new Device { Id = guide.DeviceId, Name = guide.DeviceName } },
                                                     GuideType = new GuideType { Id = guide.GuideTypeId, Name = guide.GuideTypeName }
                                                 });
                        }
                        return new GuideListFeedback(true, null, guideList, total);
                    }
                }
            }
            catch (Exception e)
            {

                return new GuideListFeedback(false, "Cannot get list Guide");
            }
        }
        public Feedback<Guide> InsertGuide(Guide guide) {
            var qnew = new Guide
            {
                Title = guide.Title,
                TitleSearch=guide.TitleSearch,
                TitleUrl = "title",
                ContentText ="hehefjsdhfksdhfsdkfhsdk",
                ImageUrl="image",
                ContentHtml=guide.ContentHtml,
                Views = 0,
                Reports = 0,
                Comments=0,
                Point = 0,
                UpVotes = 0,
                DownVotes = 0,
                PostedBy = guide.PostedBy,
                PostedDate = DateTime.Now,
                //UpdatedBy=guide.PostedBy,
                //MappingCategories=null,
                RateTimes=0,
                TotalRates=0,
                IsDeleted = false,
                isApproved=false,
                ModelID=guide.ModelID,
                GuideTypeId = guide.GuideTypeId,
              
            };

            try
            {
                // Add to DB
                ORMDbContext.Guides.Add(qnew);
                ORMDbContext.SaveChanges();
                return new Feedback<Guide>(true, null, qnew);
            }
            catch (Exception e)
            {
                return new Feedback<Guide>(false, "Đã Có Lỗi Xảy Ra");
            }
        }

        public Feedback<Guide> UpdateGuide(Guide guide)
        {
            try
            {
                using (var entities = new OrmEntities())
                {
                    var Guide = entities.Guides.SingleOrDefault(i => i.Id == guide.Id && !guide.IsDeleted);

                    if (Guide == null)
                        return new Feedback<Guide>(false,"guide khong ton tai");

                    Guide.Title = guide.Title;
                    Guide.TitleSearch = guide.TitleSearch;
                    Guide.ContentHtml = guide.ContentHtml;
                    Guide.ModelID = guide.ModelID;
                    Guide.GuideTypeId = guide.GuideTypeId;
                    Guide.UpdatedBy = guide.UpdatedBy;
                    Guide.LastUpdate = DateTime.Now;

                    entities.SaveChanges();

                    return new Feedback<Guide>(true, null, Guide);
                }
            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Guide>(false, Messages.GeneralError);
            }
        }

        public Feedback<GuideComment> UpdateGuideComment(GuideComment comment)
        {
            try
            {
                using (var entities = new OrmEntities())
                {
                    var Comment = entities.GuideComments.SingleOrDefault(i => i.Id == comment.Id && !comment.IsDeleted);

                    if (Comment == null)
                        return new Feedback<GuideComment>(false, "comment khong ton tai");

                    
                    Comment.ContentHtml = comment.ContentHtml;
                    //Guide.ModelID = guide.ModelID;
                   // Guide.GuideTypeId = guide.GuideTypeId;
                    //Guide.UpdatedBy = q.UpdatedById;
                    Comment.LastUpdate = DateTime.Now;

                    entities.SaveChanges();

                    return new Feedback<GuideComment>(true, null, Comment);
                }
            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<GuideComment>(false, Messages.GeneralError);
            }
        }


        public Feedback DeleteGuide(int id)
        {
            try
            {
                using (var entities = new OrmEntities())
                {
                    Guide q = entities.Guides.Single(i => i.Id == id);
                    q.IsDeleted = true;
                    entities.SaveChanges();

                    return new Feedback(true);
                }
            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback(false, Messages.GeneralError);
            }
        }

        public GuideListFeedback GetGuideDevice(int condition, PageInfo pageInfo, int deviceId, int manufacturerId, int modelId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in ORMDbContext.Guides
                                join u in ORMDbContext.Users on q.PostedBy equals u.Id
                                join m in ORMDbContext.Models on q.ModelID equals m.Id
                                join d in ORMDbContext.Devices on m.DeviceId equals d.Id 
                                join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                join gt in ORMDbContext.GuideTypes on q.GuideTypeId equals gt.Id
                                where !q.IsDeleted & d.Id == condition && q.isApproved
                                orderby q.PostedDate descending
                                select new
                                {
                                    q.Id,
                                    q.GuideTypeId,
                                    q.Title,
                                    q.TitleSearch,
                                    q.TitleUrl,
                                    q.PostedDate,
                                    q.Views,
                                    q.Point,
                                    q.UpVotes,
                                    q.DownVotes,
                                    q.ContentHtml,
                                    q.Comments,
                                    q.PostedBy,
                                    u.Username,
                                    q.ModelID,
                                    DeviceId = d.Id,
                                    ManufacturerId = mf.Id,
                                    ModelName = m.Name,
                                    DeviceName = d.Name,
                                    ManufacturerName = mf.Name,
                                    GuideTypeName = gt.Name,
                                };
                    if (modelId != 0)
                    {
                        query = query.Where(q => q.ModelID == modelId);
                    }
                    else
                    {
                        if (deviceId != 0)
                        {
                            query = query.Where(q => q.DeviceId == deviceId);
                        }
                        if (manufacturerId != 0)
                        {
                            query = query.Where(q => q.ManufacturerId == manufacturerId);
                        }
                    }
                    var total = query.Count();
                    query = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                    var GuideList = new List<Guide>();
                    foreach (var Guide in query)
                    {
                        GuideList.Add(new Guide
                        {
                            Id = Guide.Id,
                            GuideTypeId = Guide.GuideTypeId,
                            Title = Guide.Title,
                            TitleSearch = Guide.TitleSearch,
                            TitleUrl = Guide.TitleUrl,
                            PostedDate = Guide.PostedDate,
                            Views = Guide.Views,
                            Point = Guide.Point,
                            UpVotes = Guide.UpVotes,
                            DownVotes = Guide.DownVotes,
                            ContentHtml = Guide.ContentHtml,
                            Comments = Guide.Comments,
                            PostedByUser = new user { Id = Guide.PostedBy, Username = Guide.Username },
                            //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                            Model = new Model { Id = Guide.ModelID, Name = Guide.ModelName, Manufacturer = new Manufacturer { Name = Guide.ManufacturerName }, Device = new Device { Name = Guide.DeviceName } },
                            GuideType = new GuideType { Id = Guide.GuideTypeId, Name = Guide.GuideTypeName }
                        });
                    }
                    return new GuideListFeedback(true, null, GuideList, total);
                }

            }
            catch (Exception e)
            {

                return new GuideListFeedback(false, "Cannot get list Guide");
            }
        }

        public GuideListFeedback GetGuideUseful(PageInfo pageInfo, int deviceId, int manufacturerId, int modelId, string stringToSearch, int guideTypeId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    if (stringToSearch != null && stringToSearch != "")
                    {
                        //var queryString = "SELECT * FROM Guides WHERE isDeleted='false' and isApproved='true' and dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) > 0 order by dbo.Levenstein({0}, Title+convert(nvarchar,ContentHtml )) desc, UpVotes desc";
                        var queryString =
                            "SELECT p.* FROM dbo.Guides AS p INNER JOIN FREETEXTTABLE(Guides, Title, {0}, LANGUAGE 'Vietnamese') AS ft ON ft.[KEY] = p.Id and isDeleted = 'false'  and isApproved='true' ORDER BY ft.[Rank] DESC, UpVotes desc";
                       
                        var query = (ORMDbContext as System.Data.Entity.Infrastructure.IObjectContextAdapter)
                              .ObjectContext.ExecuteStoreQuery<Guide>(queryString, stringToSearch).ToList();
                        var total = query.Count();
                        var result = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var GuideList = new List<Guide>();
                        foreach (var Guide in result)
                        {
                            Guide.Model = ORMDbContext.Models.SingleOrDefault(m => m.Id == Guide.ModelID);
                            var device = ORMDbContext.Devices.SingleOrDefault(m => m.Id == Guide.Model.DeviceId);
                            var manufacturer = ORMDbContext.Manufacturers.SingleOrDefault(m => m.Id == Guide.Model.ManufacturerId);
                            GuideList.Add(new Guide
                            {
                                Id = Guide.Id,
                                Title = Guide.Title,
                                TitleUrl = Guide.TitleUrl,
                                TitleSearch = Guide.TitleSearch,
                                PostedDate = Guide.PostedDate,
                                Views = Guide.Views,
                                Point = Guide.Point,
                                UpVotes = Guide.UpVotes,
                                DownVotes = Guide.DownVotes,
                                ContentHtml = Guide.ContentHtml,
                                Comments = Guide.Comments,
                                PostedByUser = ORMDbContext.Users.SingleOrDefault(u => u.Id == Guide.PostedBy),
                                //ModelWithAllInfo = new ModelWithAllInformation { Id = Guide.ModelId, ModelName = Guide.ModelName, DeviceName = Guide.DeviceName, ManufactureName = Guide.ManufacturerName }   
                                Model = new Model { Id = Guide.ModelID, Name = Guide.Model.Name, Manufacturer = new Manufacturer { Id = manufacturer.Id, Name = manufacturer.Name }, Device = new Device { Id = device.Id, Name = device.Name } },
                                GuideType = ORMDbContext.GuideTypes.SingleOrDefault(gt => gt.Id == Guide.GuideTypeId)
                            });
                        }
                        return new GuideListFeedback(true, null, GuideList, total);
                    }
                    else
                    {
                        var query = from q in ORMDbContext.Guides
                                    join u in ORMDbContext.Users on q.PostedBy equals u.Id
                                    join m in ORMDbContext.Models on q.ModelID equals m.Id
                                    join d in ORMDbContext.Devices on m.DeviceId equals d.Id
                                    join mf in ORMDbContext.Manufacturers on m.ManufacturerId equals mf.Id
                                    join gt in ORMDbContext.GuideTypes on q.GuideTypeId equals gt.Id
                                    where !q.IsDeleted && q.isApproved
                                    orderby q.UpVotes descending
                                    select new
                                    {
                                        q.Id,
                                        q.GuideTypeId,
                                        q.Title,
                                        q.TitleUrl,
                                        q.TitleSearch,
                                        q.PostedDate,
                                        q.Views,
                                        q.Point,
                                        q.UpVotes,
                                        q.DownVotes,
                                        q.ContentHtml,
                                        q.Comments,
                                        q.PostedBy,
                                        u.Username,
                                        u.AvatarUrl,
                                        q.ModelID,
                                        DeviceId = d.Id,
                                        ManufacturerId = mf.Id,
                                        ModelName = m.Name,
                                        DeviceName = d.Name,
                                        ManufacturerName = mf.Name,
                                        GuideTypeName = gt.Name,
                                    };
                        if (modelId != 0)
                        {
                            query = query.Where(q => q.ModelID == modelId);
                        }
                        else
                        {
                            if (deviceId != 0)
                            {
                                query = query.Where(q => q.DeviceId == deviceId);
                            }
                            if (manufacturerId != 0)
                            {
                                query = query.Where(q => q.ManufacturerId == manufacturerId);
                            }
                        }
                        if(guideTypeId != 0)
                        {
                            query = query.Where(q => q.GuideTypeId == guideTypeId);
                        }
                        var total = query.Count();
                        query = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                        var GuideList = new List<Guide>();
                        foreach (var Guide in query)
                        {
                            GuideList.Add(new Guide
                            {
                                Id = Guide.Id,
                                GuideTypeId = Guide.GuideTypeId,
                                Title = Guide.Title,
                                TitleUrl = Guide.TitleUrl,
                                TitleSearch = Guide.TitleSearch,
                                PostedDate = Guide.PostedDate,
                                Views = Guide.Views,
                                Point = Guide.Point,
                                UpVotes = Guide.UpVotes,
                                DownVotes = Guide.DownVotes,
                                ContentHtml = Guide.ContentHtml,
                                Comments = Guide.Comments,
                                PostedByUser = ORMDbContext.Users.SingleOrDefault(u => u.Id == Guide.PostedBy),
                                //ModelWithAllInfo = new ModelWithAllInformation { Id = question.ModelId, ModelName = question.ModelName, DeviceName = question.DeviceName, ManufactureName = question.ManufacturerName }
                                Model = new Model { Id = Guide.ModelID, Name = Guide.ModelName, Manufacturer = new Manufacturer { Id = Guide.ManufacturerId, Name = Guide.ManufacturerName }, Device = new Device { Id = Guide.DeviceId, Name = Guide.DeviceName } },
                                GuideType = new GuideType { Id = Guide.GuideTypeId, Name = Guide.GuideTypeName }
                            });
                        }
                        return new GuideListFeedback(true, null, GuideList, total);
                    }
                    
                }

            }
            catch (Exception e)
            {

                return new GuideListFeedback(false, "Cannot get list Guide");
            }
        }


        public Feedback<Guide> DeleteAnComment(int guideId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var guide = ORMDbContext.Guides.Single(i => i.Id == guideId);
                    guide.Comments--;
                    ORMDbContext.SaveChanges();
                    return new Feedback<Guide>(true, null, guide);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Guide>(false, Messages.GeneralError);
            }
        }
        public Feedback<Guide> VoteGuide(int GuideId, int userId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var Guide = ORMDbContext.Guides.Single(i => i.Id == GuideId);
                    var GuideVote = ORMDbContext.VotedGuides.SingleOrDefault(t => t.GuideId == GuideId && t.VotedById == userId);
                    var User = ORMDbContext.Users.Find(Guide.PostedBy);
                    //Do not allow seft voting
                    if (Guide.PostedBy == userId)
                    {
                        return new Feedback<Guide>(false, Messages.VoteGuide_SeftVoting, null);
                    }

                    //Already voted up --> Remove this vote
                    if (GuideVote != null)
                    {
                        ORMDbContext.VotedGuides.Remove(GuideVote);
                        Guide.UpVotes--;
                        User.Ponit -= KeyObject.PointConfig.Like;
                    }
                    //Not vote yet (Insert)
                    else
                    {
                        GuideVote = new VotedGuide()
                        {
                            IsUpVote = true,
                            GuideId = GuideId,
                            VotedById = userId,
                            VotedDate = DateTime.Now,
                        };
                        ORMDbContext.VotedGuides.Add(GuideVote);
                        Guide.UpVotes++;
                        User.Ponit += KeyObject.PointConfig.Like;
                    }

                    ORMDbContext.SaveChanges();

                    return new Feedback<Guide>(true, null, Guide);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Guide>(false, Messages.GeneralError);
            }
        }
        public GuideListFeedback GetGuideBookmarks(int userId, PageInfo pageInfo)
        {

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in ORMDbContext.Guides
                                join qb in ORMDbContext.GuideBookmarks on q.Id equals qb.GuideId
                                where !q.IsDeleted && qb.BookmarkById == userId
                                orderby qb.BookmarkDate descending
                                select new
                                {
                                    q.Id,
                                    q.Title,
                                };
                    var total = query.Count();
                    query = query.Skip((pageInfo.PageNo - 1) * pageInfo.PageSize).Take(pageInfo.PageSize);
                    var guideList = new List<Guide>();
                    foreach (var guide in query)
                    {
                        guideList.Add(new Guide
                        {
                            Id = guide.Id,
                            Title = guide.Title,
                        });
                    }
                    return new GuideListFeedback(true, null, guideList, total);

                }
            }
            catch (Exception e)
            {

                return new GuideListFeedback(false, "Cannot get list guide");
            }
        }

        public Feedback<IList<Guide>> GetGuideBookmarks(int userId)
        {

            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in ORMDbContext.Guides
                                join qb in ORMDbContext.GuideBookmarks on q.Id equals qb.GuideId
                                join u in ORMDbContext.Users on q.PostedBy equals u.Id
                                where !q.IsDeleted && q.isApproved && qb.BookmarkById == userId
                                orderby qb.BookmarkDate descending
                                select new
                                {
                                    q.Id,
                                    q.Title,
                                    q.PostedBy,
                                    u.Username,
                                    q.PostedDate,
                                    q.Views,
                                    q.UpVotes,
                                    q.Comments,
                                };
                    var guideList = new List<Guide>();
                    foreach (var guide in query)
                    {
                        guideList.Add(new Guide
                        {
                            Id = guide.Id,
                            Title = guide.Title,
                            PostedByUser = new user{Id = guide.PostedBy, Username = guide.Username},
                            PostedDate = guide.PostedDate,
                            Views = guide.Views,
                            UpVotes = guide.UpVotes,
                            Comments = guide.Comments,
                        });
                    }
                    return new Feedback<IList<Guide>>(true, null, guideList);

                }
            }
            catch (Exception e)
            {

                Logger.Error(e);
                return new Feedback<IList<Guide>>(false, Messages.GeneralError);
            }
        }
        public Feedback<Guide> AddBookmarkAnGuide(int guideId, int userId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var guide = ORMDbContext.Guides.Single(i => i.Id == guideId);
                    var guideBookmark = ORMDbContext.GuideBookmarks.SingleOrDefault(t => t.GuideId == guideId && t.BookmarkById == userId);

                    //Already add bookmark --> Remove it
                    if (guideBookmark != null)
                    {
                        ORMDbContext.GuideBookmarks.Remove(guideBookmark);
                        //question.Reports--;
                    }
                    //Not reported yet (Insert)
                    else
                    {
                        guideBookmark = new GuideBookmark()
                        {
                            GuideId = guideId,
                            BookmarkById = userId,
                            BookmarkDate = DateTime.Now,
                        };
                        ORMDbContext.GuideBookmarks.Add(guideBookmark);
                        //question.Reports++;
                    }

                    ORMDbContext.SaveChanges();

                    return new Feedback<Guide>(true, null, guide);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Guide>(false, Messages.GeneralError);
            }
        }

        #region Reported Guide
        public Feedback<Guide> ReportGuide(int guideId, string reason, int userId)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var guide = ORMDbContext.Guides.Single(i => i.Id == guideId);
                    var guideReport = ORMDbContext.ReportedGuides.SingleOrDefault(t => t.GuideId == guideId && t.ReportedById == userId);

                    //Already reported --> Remove this report
                    if (guideReport != null)
                    {
                        ORMDbContext.ReportedGuides.Remove(guideReport);
                        guide.Reports--;
                    }
                    //Not reported yet (Insert)
                    else
                    {
                        guideReport = new ReportedGuide()
                        {
                           GuideId = guideId,
                            Reason = reason,
                            ReportedById = userId,
                            ReportedDate = DateTime.Now,
                            IsIgnored = false,
                        };
                        ORMDbContext.ReportedGuides.Add(guideReport);
                        guide.Reports++;
                    }

                    ORMDbContext.SaveChanges();

                    return new Feedback<Guide>(true, null, guide);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<Guide>(false, Messages.GeneralError);
            }
        }

        public Feedback<IList<Guide>> GetAllReportedGuide(int minReportNumber)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = from q in ORMDbContext.Guides
                                from u in ORMDbContext.Users
                                where !q.IsDeleted && q.Reports >= minReportNumber && q.PostedBy == u.Id
                                orderby q.Reports descending
                                select new
                                {
                                    q.Id,
                                    q.Title,
                                    q.TitleUrl,
                                    q.Reports,
                                    q.PostedDate,
                                    q.PostedBy,
                                    u.Username
                                };
                    List<Guide> rq = new List<Guide>();
                    foreach (var q in query)
                    {
                        rq.Add(new Guide
                        {
                            Id = q.Id,
                            Title = q.Title,
                            TitleUrl = q.TitleUrl,
                            PostedDate = q.PostedDate,
                            Reports = q.Reports,
                            PostedByUser = new user
                            {
                                Id = q.PostedBy,
                                Username = q.Username
                            },
                        });
                    }

                    return new Feedback<IList<Guide>>(true, null, rq);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<IList<Guide>>(false, Messages.GeneralError);
            }
        }

        public Feedback<IList<ReportedGuide>> GetReportedGuideDetails(int id)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    var query = ORMDbContext.ReportedGuides.Where(i => i.GuideId == id)
                        .Select(i => new
                        {
                            i.Id,
                            i.ReportedDate,
                            i.Reason,
                            i.ReportedBy.Username
                        });

                    List<ReportedGuide> rq = new List<ReportedGuide>();
                    foreach (var q in query)
                    {
                        rq.Add(new ReportedGuide
                        {
                            Id = q.Id,
                            ReportedDate = q.ReportedDate,
                            Reason = q.Reason,
                            ReportedBy = new user
                            {
                                Username = q.Username
                            }
                        });
                    }
                    return new Feedback<IList<ReportedGuide>>(true, null, rq);

                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback<IList<ReportedGuide>>(false, Messages.GeneralError);
            }
        }

        public Feedback DeleteReportedGuide(int id)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    Guide q = ORMDbContext.Guides.Single(i => i.Id == id);
                    q.IsDeleted = true;
                    user User = ORMDbContext.Users.Find(q.PostedBy);
                    User.Ponit += KeyObject.PointConfig.Spam;
                    ORMDbContext.SaveChanges();

                    return new Feedback(true);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback(false, Messages.GeneralError);
            }
        }

        public Feedback DeleteFalseReports(int id)
        {
            try
            {
                using (var ORMDbContext = new OrmEntities())
                {
                    // Delete attached reports of the quesion
                    List<ReportedGuide> rq = ORMDbContext.ReportedGuides.Where(i => i.GuideId == id).ToList();
                    foreach (ReportedGuide i in rq)
                    {
                        i.IsIgnored = true;
                    }
                    //set report number to zero
                    Guide q = ORMDbContext.Guides.Single(i => i.Id == id);
                    q.Reports = 0;
                    ORMDbContext.SaveChanges();

                    return new Feedback(true);
                }

            }
            catch (Exception e)
            {
                Logger.Error(e);
                return new Feedback(false, Messages.GeneralError);
            }
        }

        #endregion
    
    }

   

}
